package com.sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Khaled Lela
 */
public class PeopleSql {

    private Connection con;
    private PreparedStatement ps;
    private ResultSet rs;
    private boolean error;
    private Object[][] data;
    private int personNums;
    private int count;

    public PeopleSql() {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            con = DriverManager.getConnection("jdbc:derby:BreadDB;");
        } catch (Exception ex) {
        }
    }

    //insert People data Row
    public boolean setPeopleRow(int id, String name, int personNUm, int PeopleID, String category) {
        try {
            ps = con.prepareStatement("insert into People( PersonID, PersonName , Person_Num , DistributorID , Category ) "
                    + "values"
                    + " (?,?,?,?,?)");
            ps.setInt(1, id);
            ps.setString(2, name);
            ps.setInt(3, personNUm);
            ps.setInt(4, PeopleID);
            ps.setString(5, category);
            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        }
        return error;
    }

    //Update People data Row   case (1)
    public boolean updatePeopleRow(int id, String name, int personNum, int disID, String category) {

        try {
            ps = con.prepareStatement("UPDATE People "
                    + "SET PersonName =? , Person_Num =? , DistributorID =? , Category =? "
                    + " WHERE PersonID = ? ");

            ps.setString(1, name);
            ps.setInt(2, personNum);
            ps.setInt(3, disID);
            ps.setString(4, category);
            ps.setInt(5, id);

            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        }
        return error;
    }

    //Update Name & person Number   case(2)
    public boolean updatePeopleRow(int id, String name, int personNUm, String category) {
        try {
            ps = con.prepareStatement("UPDATE People "
                    + "SET PersonName =? , Person_Num =? , Category =? "
                    + " WHERE PersonID = ? ");
            ps.setString(1, name);
            ps.setInt(2, personNUm);
            ps.setString(3, category);
            ps.setInt(4, id);
            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        }
        return error;
    }

    //Update Name & Distributor ID Case(3)
    public boolean updatePeopleRow(int id, String name, int disID) {
        try {
            ps = con.prepareStatement("UPDATE People "
                    + "SET PersonName =? , DistributorID =? "
                    + " WHERE PersonID = ? ");
            ps.setString(1, name);
            ps.setInt(2, disID);
            ps.setInt(3, id);
            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        }
        return error;
    }

    //Update family Num & Distributor ID Case(4)
    public boolean updatePeopleRow(int id, int personNum, int disID, String category) {
        try {
            ps = con.prepareStatement("UPDATE People "
                    + "SET Person_Num =? , DistributorID =? , Category =? "
                    + " WHERE PersonID = ? ");
            ps.setInt(1, personNum);
            ps.setInt(2, disID);
            ps.setString(3, category);
            ps.setInt(4, id);
            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        }
        return error;
    }

    //Update Name Only Case(5)
    public boolean updatePeopleRow(int id, String name) {
        try {
            ps = con.prepareStatement("UPDATE People "
                    + "SET PersonName =?  "
                    + " WHERE PersonID = ? ");
            ps.setString(1, name);
            ps.setInt(2, id);
            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        }
        return error;
    }

    //Update family Num only ID Case(6)
    public boolean updatePeopleRow(int id, int personNum, String category) {
        try {
            ps = con.prepareStatement("UPDATE People "
                    + "SET Person_Num =? , Category =? "
                    + " WHERE PersonID = ? ");
            ps.setInt(1, personNum);
            ps.setString(2, category);
            ps.setInt(3, id);
            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        }
        return error;
    }

    //Update  Distributor ID Only Case(7)
    public boolean updatePeopleRow(int id, int disID) {
        try {
            ps = con.prepareStatement("UPDATE People "
                    + "SET DistributorID =? "
                    + " WHERE PersonID = ? ");

            ps.setInt(1, disID);
            ps.setInt(2, id);
            ps.executeUpdate();
        } catch (Exception ex) {
            error = true;
        }
        return error;
    }

    //Check If person ID exist or Not
    public boolean isPersonIDExist(int id) {
        boolean isExist = false;
        try {
            ps = con.prepareStatement("SELECT PersonID "
                    + " FROM People ");
            rs = ps.executeQuery();
            while (rs.next()) {
                if (rs.getInt(1) == id) {
                    isExist = true;
                }
            }
        } catch (Exception e) {
        }
        return isExist;
    }

    //get People data row to be displayed on Table
    public Object[][] getPeopleRow() {
        ArrayList list1 = new ArrayList();
        ArrayList list2 = new ArrayList();
        ArrayList list3 = new ArrayList();
        ArrayList list4 = new ArrayList();
        ArrayList list5 = new ArrayList();
        ArrayList list6 = new ArrayList();
        try {
            ps = con.prepareStatement("SELECT P.PersonID , P.PersonName , P.Person_Num , P.Category, "
                    + " D.Name , D.Region "
                    + " FROM People P , Distributor D "
                    + " WHERE P.DistributorID = D.DistributorID "
                    + " ORDER BY P.PersonID ");
            rs = ps.executeQuery();
            while (rs.next()) {
                list1.add(rs.getInt("PersonID") + "");
                list2.add(rs.getString("PersonName"));
                list3.add(rs.getInt("Person_Num") + "");
                list4.add(rs.getString("Name"));
                list5.add(rs.getString("Region"));
                list6.add(rs.getString("Category"));
            }
            data = new Object[list1.size()][6];
            int i = 0;
            for (int j = 0; j < list1.size(); j++) {
                data[j][5] = list1.get(i);
                data[j][4] = list2.get(i);
                data[j][3] = list3.get(i);
                data[j][2] = list4.get(i);
                data[j][1] = list5.get(i);
                data[j][0] = list6.get(i);
                i++;
            }
        } catch (Exception e) {
        }
        return data;
    }

    // get number of record on people table
    public int countPersonID() {
        try {
            ps = con.prepareStatement(" SELECT COUNT(*) FROM People");
            rs = ps.executeQuery();
            while (rs.next()) {
                personNums = rs.getInt(1);
            }
        } catch (Exception ex) {
        }
        return personNums;
    }
        public int getCategoryCount(String categoryName) {
        try {
            ps = con.prepareStatement(" SELECT COUNT(*) FROM People"
                    + " WHERE "
                    + " Category =?");
            ps.setString(1,categoryName);
            rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return count;
    }
}
